﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Library;
namespace QuanLyThi
{
    public partial class FrmKetQuaThiTheoLop : Form
    {
        public static string mConnect = FrmMain.mConnect;
        private string msql = "";
        public DGC_Library mLibrary = new DGC_Library(mConnect);

        public FrmKetQuaThiTheoLop()
        {
            InitializeComponent();
            this.Load();           
        }

        private void button6_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void Load()
        {

            msql = "Select * from tb_CTdaotao order by CT_daotao";
            DataTable dt = mLibrary.GetDataTable(msql, "table");
            cchuongtrinhdt.DataSource = dt;
            cchuongtrinhdt.DisplayMember = "ct_daotao";
            cchuongtrinhdt.ValueMember = "MS_daotao";

         
           
        }

        private void cchuongtrinhdt_SelectionChangeCommitted(object sender, EventArgs e)
        {
            msql = "Select a.* from tb_Khoadaotao a " +
                  " inner join tb_cauhinhCtdaotao b " +
                  " on a.MS_Khoadaotao=b.ms_Khoadaotao " +
                  " Where b.ms_daotao="+cchuongtrinhdt.SelectedValue.ToString();
            DataTable dt1 = mLibrary.GetDataTable(msql, "table");
            ckhoa.DataSource = dt1;
            ckhoa.DisplayMember = "khoadaotao";
            ckhoa.ValueMember = "MS_khoadaotao";
        }

        private void ckhoa_SelectionChangeCommitted(object sender, EventArgs e)
        {
            msql = "Select * from tb_Lopdaotao Where MS_Khoadaotao="+ckhoa.SelectedValue.ToString();
            DataTable dt2 = mLibrary.GetDataTable(msql, "table");
            clop.DataSource = dt2;
            clop.DisplayMember = "tenlop";
            clop.ValueMember = "MS_lop";
        }

        private void clop_SelectionChangeCommitted(object sender, EventArgs e)
        {    
            //string mslop = clop.SelectedValue.ToString();
            //string mskhoa = ckhoa.SelectedValue.ToString();
            //msql = "Select a.ms_hssv,a.hovaten,a.gioitinh,a.namsinh,a.diachi,a.dienthoai,a.mail from tb_hssv a " +
            //       " inner join tb_hssvlop b on a.ms_hssv=b.ms_hssv " +
            //       " where b.ms_lop='"+clop.SelectedValue.ToString()+"' Order by a.hovaten";
            //DataTable dt1 = mLibrary.GetDataTable(msql, "tb1");
            //dataGridView1.AutoGenerateColumns = false;
            //dataGridView1.DataSource = dt1;        
            LoadGrid();
        }


        void LoadGrid()
        {
            try
            {
                string malop = clop.SelectedValue.ToString();
                DataTable dt = null;
                if (ratatca.Checked)
                {
                    msql = "Select a.ms_hssv,a.hovaten, case gioitinh when 1 then N'Nam' Else N'Nữ' End as gioitinh, Dienthoai,Mail," +
                           " e.mondaotao,d.diemthi, Case d.ketqua when 1 then N'Đạt' When 0 then N'Hỏng' Else '' End as Ketqua, " +
                           " d.lanthi,f.tenloaithi from tb_hssv a inner join tb_hssvlop b on a.ms_hssv=b.ms_hssv inner join tb_cauhinhlop c" +
                           " on b.ms_lop=c.ms_lop inner join tb_hssvthi d on a.ms_hssv=d.ms_hssv and c.ms_mondaotao=d.ms_mondaotao " +
                           " and b.ms_lop=d.ms_lop inner join tb_mondaotao e on d.ms_mondaotao=e.MS_mondaotao inner join tb_loaihinhthi f " +
                           " on d.ms_loaithi=f.ms_loaithi where c.ms_lop='" + malop + "' " +
                           " Order by a.hovaten,e.mondaotao,d.lanthi";
                }

                if (radathi.Checked)
                {
                    msql = "Select a.ms_hssv,a.hovaten, case gioitinh when 1 then N'Nam' Else N'Nữ' End as gioitinh, Dienthoai,Mail," +
                               " e.mondaotao,d.diemthi, Case d.ketqua when 1 then N'Đạt' When 0 then N'Hỏng' Else '' End as Ketqua, " +
                               " d.lanthi,f.tenloaithi from tb_hssv a inner join tb_hssvlop b on a.ms_hssv=b.ms_hssv inner join tb_cauhinhlop c" +
                               " on b.ms_lop=c.ms_lop inner join tb_hssvthi d on a.ms_hssv=d.ms_hssv and c.ms_mondaotao=d.ms_mondaotao " +
                               " and b.ms_lop=d.ms_lop inner join tb_mondaotao e on d.ms_mondaotao=e.MS_mondaotao inner join tb_loaihinhthi f " +
                               " on d.ms_loaithi=f.ms_loaithi where c.ms_lop='" + malop + "' and d.diemthi is not null" +
                               " Order by a.hovaten,e.mondaotao,d.lanthi";
                }
                if (rachuathi.Checked)
                {
                    msql = "Select a.ms_hssv,a.hovaten, case gioitinh when 1 then N'Nam' Else N'Nữ' End as gioitinh, Dienthoai,Mail," +
                          " e.mondaotao,d.diemthi, Case d.ketqua when 1 then N'Đạt' When 0 then N'Hỏng' Else '' End as Ketqua, " +
                          " d.lanthi,f.tenloaithi from tb_hssv a inner join tb_hssvlop b on a.ms_hssv=b.ms_hssv inner join tb_cauhinhlop c" +
                          " on b.ms_lop=c.ms_lop inner join tb_hssvthi d on a.ms_hssv=d.ms_hssv and c.ms_mondaotao=d.ms_mondaotao " +
                          " and b.ms_lop=d.ms_lop inner join tb_mondaotao e on d.ms_mondaotao=e.MS_mondaotao inner join tb_loaihinhthi f " +
                          " on d.ms_loaithi=f.ms_loaithi where c.ms_lop='" + malop + "' and d.diemthi is null" +
                          " Order by a.hovaten,e.mondaotao,d.lanthi";
                }
                dt = mLibrary.GetDataTable(msql, "a");
                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = dt;
            }
            catch (Exception)
            {
                MessageBox.Show("Bạn hãy chọn Chương trình,Khoa trước khi chọn lớp", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
          
        }

        private void radathi_CheckedChanged(object sender, EventArgs e)
        {
            if (radathi.Checked)
            {
                string malop = clop.SelectedValue.ToString();
                DataTable dt = null;
                msql = "Select a.ms_hssv,a.hovaten, case gioitinh when 1 then N'Nam' Else N'Nữ' End as gioitinh, Dienthoai,Mail," +
                               " e.mondaotao,d.diemthi, Case d.ketqua when 1 then N'Đạt' When 0 then N'Hỏng' Else '' End as Ketqua, " +
                               " d.lanthi,f.tenloaithi from tb_hssv a inner join tb_hssvlop b on a.ms_hssv=b.ms_hssv inner join tb_cauhinhlop c" +
                               " on b.ms_lop=c.ms_lop inner join tb_hssvthi d on a.ms_hssv=d.ms_hssv and c.ms_mondaotao=d.ms_mondaotao " +
                               " and b.ms_lop=d.ms_lop inner join tb_mondaotao e on d.ms_mondaotao=e.MS_mondaotao inner join tb_loaihinhthi f " +
                               " on d.ms_loaithi=f.ms_loaithi where c.ms_lop='" + malop + "' and d.diemthi is not null" +
                               " Order by a.hovaten,e.mondaotao,d.lanthi";
              
                dt = mLibrary.GetDataTable(msql, "a");
                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = dt;
            }
        }

        private void rachuathi_CheckedChanged(object sender, EventArgs e)
        {
            if (rachuathi.Checked)
            {
                string malop = clop.SelectedValue.ToString();
                DataTable dt = null;
                msql = "Select a.ms_hssv,a.hovaten, case gioitinh when 1 then N'Nam' Else N'Nữ' End as gioitinh, Dienthoai,Mail," +
                          " e.mondaotao,d.diemthi, Case d.ketqua when 1 then N'Đạt' When 0 then N'Hỏng' Else '' End as Ketqua, " +
                          " d.lanthi,f.tenloaithi from tb_hssv a inner join tb_hssvlop b on a.ms_hssv=b.ms_hssv inner join tb_cauhinhlop c" +
                          " on b.ms_lop=c.ms_lop inner join tb_hssvthi d on a.ms_hssv=d.ms_hssv and c.ms_mondaotao=d.ms_mondaotao " +
                          " and b.ms_lop=d.ms_lop inner join tb_mondaotao e on d.ms_mondaotao=e.MS_mondaotao inner join tb_loaihinhthi f " +
                          " on d.ms_loaithi=f.ms_loaithi where c.ms_lop='" + malop + "' and d.diemthi is null" +
                          " Order by a.hovaten,e.mondaotao,d.lanthi";
                dt = mLibrary.GetDataTable(msql, "a");
                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = dt;
            }
        }

        private void ratatca_CheckedChanged(object sender, EventArgs e)
        {
            if (ratatca.Checked)
            {
                string malop = clop.SelectedValue.ToString();
                DataTable dt = null;
                msql = "Select a.ms_hssv,a.hovaten, case gioitinh when 1 then N'Nam' Else N'Nữ' End as gioitinh, Dienthoai,Mail," +
                              " e.mondaotao,d.diemthi, Case d.ketqua when 1 then N'Đạt' When 0 then N'Hỏng' Else '' End as Ketqua, " +
                              " d.lanthi,f.tenloaithi from tb_hssv a inner join tb_hssvlop b on a.ms_hssv=b.ms_hssv inner join tb_cauhinhlop c" +
                              " on b.ms_lop=c.ms_lop inner join tb_hssvthi d on a.ms_hssv=d.ms_hssv and c.ms_mondaotao=d.ms_mondaotao " +
                              " and b.ms_lop=d.ms_lop inner join tb_mondaotao e on d.ms_mondaotao=e.MS_mondaotao inner join tb_loaihinhthi f " +
                              " on d.ms_loaithi=f.ms_loaithi where c.ms_lop='" + malop + "' " +
                              " Order by a.hovaten,e.mondaotao,d.lanthi";
                dt = mLibrary.GetDataTable(msql, "a");
                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = dt;
            }
        }

     
    }
}
